#!usr/bin/env python
# *- coding:utf-8 -*-
# Andy Create @ 11/20/2019 5:02 PM
import os
import xlrd

from flask import Blueprint, request, render_template, redirect, url_for
from flask_excel.utils import SqlHelper

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
book = Blueprint('book', __name__, template_folder='templates')
sqler = SqlHelper()


@book.route('/edit', endpoint='edit_book', methods=['GET', 'POST', ])
def edit():
    id = request.args.get('id')
    sql = "select * from Book where id=%s"
    result = sqler.fetchone(sql, id)
    book = {}
    book['title'] = result[0]
    book['author'] = result[1]
    book['price'] = result[2]
    book['id'] = id

    if request.method == 'GET':
        return render_template('edit.html', book=book)
    else:
        id = request.form.get('id')
        title = request.form.get('title')
        author = request.form.get('author')
        price = request.form.get('price')
        sql = "update Book set title=%s, author=%s, price=%s where id=%s"
        sqler.commit_data(sql, title, author, price, id)
        return redirect('index')


@book.route('/add', endpoint='add_book', methods=['GET', 'POST', ])
def add():
    if request.method == 'GET':
        return render_template('add.html')
    else:
        title = request.form.get('title')
        author = request.form.get('author')
        price = request.form.get('price')
        sql = "Insert into Book(title, author, price) values(%s, %s,%s)"
        sqler.commit_data(sql, title, author, price)
        return redirect('index')


@book.route('/delete', endpoint='delete_book', methods=['GET', ])
def delete():
    id = request.args.get('id')
    sql = "delete from Book where id=%s"
    sqler.commit_data(sql, id)
    return redirect('index')


@book.route('/upload', methods=['GET', 'POST'], endpoint='upload')
def upload():
    if request.method == 'GET':
        return render_template('upload.html')
    elif request.method == 'POST':
        file = request.files.get('file')
        file_path = os.path.join(BASE_DIR, 'uploads')
        file_name = os.path.join(file_path, file.filename)
        file_content = file.read()
        with open(file_name, 'wb') as f:
            f.write(file_content)
            # TODO file.chunks()?

        if file:
            data = xlrd.open_workbook(file_contents=file_content)
            table = data.sheets()[0]

            sql = "Insert into Book(title, author, price) values(%s,%s, %s);"
            rows = table.nrows
            cols = table.ncols
            for i in range(1, rows):
                title = table.row_values(i)[0]
                author = table.row_values(i)[1]
                price = table.row_values(i)[2]
                sqler.commit_data(sql, title, author, price)
        return redirect('index')


@book.route('/download', endpoint='download', methods=['GET'])
def download():
    if request.method == 'GET':
        pass
    pass
